using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using System.Web.Services;
using System.Web.Script.Services;


public abstract class BaseEntity
{
}

public class Supplier : BaseEntity
{
    private int _id;
    private string _company = string.Empty;
    private string _contactName = string.Empty;
    private string _contactTitle = string.Empty;
    private string _address = string.Empty;
    private string _city = string.Empty;
    private string _region = string.Empty;
    private string _postalCode = string.Empty;
    private string _country = string.Empty;
    private string _phone = string.Empty;
    private string _fax = string.Empty;
    private bool _active;

    public int ID
    {
        get
        {
            return _id;
        }
        set
        {
            _id = value;
        }
    }

    public string Company
    {
        get
        {
            return _company;
        }
        set
        {
            _company = value;
        }
    }

    public string ContactName
    {
        get
        {
            return _contactName;
        }
        set
        {
            _contactName = value;
        }
    }

    public string ContactTitle
    {
        get
        {
            return _contactTitle;
        }
        set
        {
            _contactTitle = value;
        }
    }

    public string Address
    {
        get
        {
            return _address;
        }
        set
        {
            _address = value;
        }
    }

    public string City
    {
        get
        {
            return _city;
        }
        set
        {
            _city = value;
        }
    }

    public string Region
    {
        get
        {
            return _region;
        }
        set
        {
            _region = value;
        }
    }

    public string PostalCode
    {
        get
        {
            return _postalCode;
        }
        set
        {
            _postalCode = value;
        }
    }

    public string Country
    {
        get
        {
            return _country;
        }
        set
        {
            _country = value;
        }
    }

    public string Phone
    {
        get
        {
            return _phone;
        }
        set
        {
            _phone = value;
        }
    }

    public string Fax
    {
        get
        {
            return _fax;
        }
        set
        {
            _fax = value;
        }
    }

    public bool Active
    {
        get
        {
            return _active;
        }
        set
        {
            _active = value;
        }
    }
}

public class Category : BaseEntity
{
    private int _id;
    private string _name = string.Empty;

    public int ID
    {
        get
        {
            return _id;
        }
        set
        {
            _id = value;
        }
    }

    public string Name
    {
        get
        {
            return _name;
        }
        set
        {
            _name = value;
        }
    }
}

public class Customer : BaseEntity
{
    private string _id;
    private string _company = string.Empty;
    private string _contactName = string.Empty;
    private string _contactTitle = string.Empty;
    private string _address = string.Empty;
    private string _city = string.Empty;
    private string _postalCode = string.Empty;
    private string _country = string.Empty;
    private string _phone = string.Empty;

    public string ID
    {
        get
        {
            return _id;
        }
        set
        {
            _id = value;
        }
    }

    public string Company
    {
        get
        {
            return _company;
        }
        set
        {
            _company = value;
        }
    }

    public string ContactName
    {
        get
        {
            return _contactName;
        }
        set
        {
            _contactName = value;
        }
    }

    public string ContactTitle
    {
        get
        {
            return _contactTitle;
        }
        set
        {
            _contactTitle = value;
        }
    }

    public string Address
    {
        get
        {
            return _address;
        }
        set
        {
            _address = value;
        }
    }

    public string City
    {
        get
        {
            return _city;
        }
        set
        {
            _city = value;
        }
    }

    public string PostalCode
    {
        get
        {
            return _postalCode;
        }
        set
        {
            _postalCode = value;
        }
    }

    public string Country
    {
        get
        {
            return _country;
        }
        set
        {
            _country = value;
        }
    }

    public string Phone
    {
        get
        {
            return _phone;
        }
        set
        {
            _phone = value;
        }
    }
}

public class Product : BaseEntity
{
    private int _id;
    private string _name = string.Empty;
    private int _categoryID;
    private string _categoryName = string.Empty;
    private int _supplierID;
    private string _supplierName = string.Empty;
    private string _quantityPerUnit = string.Empty;
    private decimal _unitPrice;
    private short _unitsInStock;
    private short _unitsOnOrder;
    private bool _discontinued;

    public int ID
    {
        get
        {
            return _id;
        }
        set
        {
            _id = value;
        }
    }

    public string Name
    {
        get
        {
            return _name;
        }
        set
        {
            _name = value;
        }
    }

    public int CategoryID
    {
        get
        {
            return _categoryID;
        }
        set
        {
            _categoryID = value;
        }
    }

    public string CategoryName
    {
        get
        {
            return _categoryName;
        }
        set
        {
            _categoryName = value;
        }
    }

    public int SupplierID
    {
        get
        {
            return _supplierID;
        }
        set
        {
            _supplierID = value;
        }
    }

    public string SupplierName
    {
        get
        {
            return _supplierName;
        }
        set
        {
            _supplierName = value;
        }
    }

    public string QuantityPerUnit
    {
        get
        {
            return _quantityPerUnit;
        }
        set
        {
            _quantityPerUnit = value;
        }
    }

    public decimal UnitPrice
    {
        get
        {
            return _unitPrice;
        }
        set
        {
            _unitPrice = value;
        }
    }

    public short UnitsInStock
    {
        get
        {
            return _unitsInStock;
        }
        set
        {
            _unitsInStock = value;
        }
    }

    public short UnitsOnOrder
    {
        get
        {
            return _unitsOnOrder;
        }
        set
        {
            _unitsOnOrder = value;
        }
    }

    public bool Discontinued
    {
        get
        {
            return _discontinued;
        }
        set
        {
            _discontinued = value;
        }
    }
}

public class PagedResult<T> where T : BaseEntity
{
    private int _total;
    private List<T> _rows;

    public int Total
    {
        get
        {
            return _total;
        }
        set
        {
            _total = value;
        }
    }

    public List<T> Rows
    {
        get
        {
            return _rows;
        }
        set
        {
            _rows = value;
        }
    }
}


[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ScriptService()]
public class DataService : WebService
{
    private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

    [WebMethod()]
    public Supplier[] GetTopTenSupplier()
    {
        List<Supplier> list = new List<Supplier>();

        using (IDbConnection cnn = CreateConnection())
        {
            using (IDbCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = "SELECT top 10 [SupplierID],   " +
                                    "       [CompanyName],  " +
                                    "       [ContactName],  " +
                                    "       [ContactTitle], " +
                                    "       [Address],      " +
                                    "       [City],         " +
                                    "       [Region],       " +
                                    "       [PostalCode],   " +
                                    "       [Country],      " +
                                    "       [Phone],        " +
                                    "       [Fax]           " +
                                    "FROM                   " +
                                    "   [Suppliers]         " +
                                    "ORDER BY [CompanyName] ASC";

                using (IDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Supplier s = BuildSupplier(rdr);

                        list.Add(s);
                    }
                }
            }
        }

        if (list.Count == 0)
        {
            return null;
        }

        for (int i = 0; i < list.Count; i++)
        {
            if (i == 0)
            {
                list[i].Active = true;
            }
            else
            {
                list[i].Active = ((i % 2) == 0);
            }
        }

        return list.ToArray();
    }

    [WebMethod()]
    public Supplier[] GetAllSupplier()
    {
        List<Supplier> list = new List<Supplier>();

        using (IDbConnection cnn = CreateConnection())
        {
            using (IDbCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText =   "SELECT [SupplierID],   " +
                                    "       [CompanyName],  " +
                                    "       [ContactName],  " +
                                    "       [ContactTitle], " +
                                    "       [Address],      " +
                                    "       [City],         " +
                                    "       [Region],       " +
                                    "       [PostalCode],   " +
                                    "       [Country],      " +
                                    "       [Phone],        " +
                                    "       [Fax]           " +
                                    "FROM                   " +
                                    "   [Suppliers]         " +
                                    "ORDER BY [CompanyName] ASC";

                using (IDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Supplier s = BuildSupplier(rdr);

                        list.Add(s);
                    }
                }
            }
        }

        if (list.Count == 0)
        {
            return null;
        }

        for (int i = 0; i < list.Count; i++)
        {
            if (i == 0)
            {
                list[i].Active = true;
            }
            else
            {
                list[i].Active = ((i % 2) == 0);
            }
        }

        return list.ToArray();
    }

    [WebMethod()]
    public Category[] GetAllCategory()
    {
        List<Category> list = new List<Category>();

        using (IDbConnection cnn = CreateConnection())
        {
            using (IDbCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = "SELECT [CategoryID], [CategoryName] FROM [Categories] ORDER BY [CategoryName] ASC";

                using (IDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Category c = new Category();

                        c.ID = rdr.IsDBNull(0) ? 0 : rdr.GetInt32(0);
                        c.Name = rdr.IsDBNull(1) ? string.Empty : rdr.GetString(1).Trim();

                        list.Add(c);
                    }
                }
            }
        }

        if (list.Count == 0)
        {
            return null;
        }

        return list.ToArray();
    }

    [WebMethod()]
    public PagedResult<Customer> GetCustomerList(int start, int max, string sortColumn, string sortOrder)
    {
        if (max == 0)
        {
            max = 10;
        }

        if (string.IsNullOrEmpty(sortColumn) || (string.Compare(sortColumn, "Company", true) == 0))
        {
            sortColumn = "CompanyName";
        }

        if (string.IsNullOrEmpty(sortOrder))
        {
            sortOrder = "ASC";
        }

        const string SQL = "SELECT  [CustomerID],                                                  " +
                           "        [CompanyName],                                                 " +
                           "        [ContactName],                                                 " +
                           "        [ContactTitle],                                                " +
                           "        [Address],                                                     " +
                           "        [City],                                                        " +
                           "        [PostalCode],                                                  " +
                           "        [Country],                                                     " +
                           "        [Phone]                                                        " +
                           "FROM                                                                   " +
                           "     (                                                                 " +
                           "        SELECT  [CustomerID],                                          " +
                           "                [CompanyName],                                         " +
                           "                [ContactName],                                         " +
                           "                [ContactTitle],                                        " +
                           "                [Address],                                             " +
                           "                [City],                                                " +
                           "                [PostalCode],                                          " +
                           "                [Country],                                             " +
                           "                [Phone],                                               " +
                           "                ROW_NUMBER() OVER (ORDER BY [{0}] {1}) AS [RowIndex]   " +
                           "         FROM                                                          " +
                           "                [Customers]                                            " +
                           "     ) AS [CustomerWithRowIndex]                                       " +
                           "WHERE                                                                  " +
                           "        ([RowIndex] > {2})                                             " +
                           "AND     ([RowIndex] <= ({2} + {3}))                                    " +
                           "                                                                       " +
                           "SELECT COUNT(CustomerID)                                               " +
                           "FROM                                                                   " +
                           "    [Customers]                                                        ";

        int total = 0;
        List<Customer> list = new List<Customer>();

        using (IDbConnection cnn = CreateConnection())
        {
            using (IDbCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = string.Format(SQL, sortColumn, sortOrder, start, max);

                using (IDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Customer c = BuildCustomer(rdr);

                        list.Add(c);
                    }

                    if ((rdr.NextResult()) && (rdr.Read()))
                    {
                        total = rdr.GetInt32(0);
                    }
                }
            }
        }

        if ((list.Count == 0) || (total == 0))
        {
            return null;
        }

        PagedResult<Customer> result = new PagedResult<Customer>();

        result.Rows = list;
        result.Total = total;

        return result;
    }

    [WebMethod()]
    public PagedResult<Product> GetProductList(int start, int max, string sortColumn, string sortOrder)
    {
        if (max == 0)
        {
            max = 10;
        }

        if (string.IsNullOrEmpty(sortColumn) || (string.Compare(sortColumn, "Name", true) == 0))
        {
            sortColumn = "ProductName";
        }
        else if (string.Compare(sortColumn, "SupplierName") == 0)
        {
            sortColumn = "CompanyName";
        }

        if (string.IsNullOrEmpty(sortOrder))
        {
            sortOrder = "ASC";
        }

        const string SQL = "SELECT  [ProductID],                                                   " +
                           "        [ProductName],                                                 " +
                           "        [CategoryID],                                                  " +
                           "        [CategoryName],                                                " +
                           "        [SupplierID],                                                  " +
                           "        [SupplierName],                                                " +
                           "        [QuantityPerUnit],                                             " +
                           "        [UnitPrice],                                                   " +
                           "        [UnitsInStock],                                                " +
                           "        [UnitsOnOrder],                                                " +
                           "        [Discontinued]                                                 " +
                           "FROM                                                                   " +
                           "     (                                                                 " +
                           "        SELECT  [P].[ProductID],                                       " +
                           "                [P].[ProductName],                                     " +
                           "                [C].[CategoryID],                                      " +
                           "                [C].[CategoryName] AS [CategoryName],                  " +
                           "                [S].[SupplierID],                                      " +
                           "                [S].[CompanyName] AS [SupplierName],                   " +
                           "                [P].[QuantityPerUnit],                                 " +
                           "                [P].[UnitPrice],                                       " +
                           "                [P].[UnitsInStock],                                    " +
                           "                [P].[UnitsOnOrder],                                    " +
                           "                [P].[Discontinued],                                    " +
                           "                ROW_NUMBER() OVER (ORDER BY [{0}] {1}) AS [RowIndex]   " +
                           "         FROM                                                          " +
                           "                [Products] AS [P]                                      " +
                           "         LEFT OUTER JOIN                                               " +
                           "                [Categories] AS [C]                                    " +
                           "         ON     [P].[CategoryID] = [C].[CategoryID]                    " +
                           "         LEFT OUTER JOIN                                               " +
                           "                [Suppliers] AS [S]                                     " +
                           "         ON     [P].[SupplierID] = [S].[SupplierID]                    " +
                           "     ) AS [ProductWithRowIndex]                                        " +
                           "WHERE                                                                  " +
                           "        ([RowIndex] > {2})                                             " +
                           "AND     ([RowIndex] <= ({2} + {3}))                                    " +
                           "                                                                       " +
                           "SELECT COUNT(ProductID)                                                " +
                           "FROM                                                                   " +
                           "    [Products]                                                         ";

        int total = 0;
        List<Product> list = new List<Product>();

        using (IDbConnection cnn = CreateConnection())
        {
            using (IDbCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = string.Format(SQL, sortColumn, sortOrder, start, max);

                using (IDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Product p = BuildProduct(rdr);

                        list.Add(p);
                    }

                    if ((rdr.NextResult()) && (rdr.Read()))
                    {
                        total = rdr.GetInt32(0);
                    }
                }
            }
        }

        if ((list.Count == 0) || (total == 0))
        {
            return null;
        }

        PagedResult<Product> result = new PagedResult<Product>();

        result.Rows = list;
        result.Total = total;

        return result;
    }

    [WebMethod()]
    public Product[] GetProductsByCategory(int categoryID)
    {
        const string SQL = "        SELECT  [P].[ProductID],                                       " +
                           "                [P].[ProductName],                                     " +
                           "                [C].[CategoryID],                                      " +
                           "                [C].[CategoryName] AS [CategoryName],                  " +
                           "                [S].[SupplierID],                                      " +
                           "                [S].[CompanyName] AS [SupplierName],                   " +
                           "                [P].[QuantityPerUnit],                                 " +
                           "                [P].[UnitPrice],                                       " +
                           "                [P].[UnitsInStock],                                    " +
                           "                [P].[UnitsOnOrder],                                    " +
                           "                [P].[Discontinued]                                     " +
                           "         FROM                                                          " +
                           "                [Products] AS [P]                                      " +
                           "         LEFT OUTER JOIN                                               " +
                           "                [Categories] AS [C]                                    " +
                           "         ON     [P].[CategoryID] = [C].[CategoryID]                    " +
                           "         LEFT OUTER JOIN                                               " +
                           "                [Suppliers] AS [S]                                     " +
                           "         ON     [P].[SupplierID] = [S].[SupplierID]                    " +
                           "         WHERE                                                         " +
                           "                [P].[CategoryID] = @p1                                 ";

        List<Product> list = new List<Product>();

        using (IDbConnection cnn = CreateConnection())
        {
            using (IDbCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = SQL;
                cmd.Parameters.Add(new SqlParameter("@p1", categoryID));

                using (IDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Product p = BuildProduct(rdr);

                        list.Add(p);
                    }
                }
            }
        }

        if (list.Count == 0)
        {
            return null;
        }

        return list.ToArray();
    }

    [WebMethod()]
    public bool AddNewSupplier(Supplier supplier)
    {

        const string SQL = "insert into [Suppliers]                         " +
                            "( [CompanyName],  [ContactName],                " +
                            " [ContactTitle], [Address], [City], [Region],   " +
                            "[PostalCode],[Country],[Phone], [Fax])          " +
                            " values (@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)";

        using (IDbConnection cnn = CreateConnection())
        {
            using (IDbCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = SQL;
                cmd.Parameters.Add(new SqlParameter("@p1", supplier.Company));
                cmd.Parameters.Add(new SqlParameter("@p2", supplier.ContactName));
                cmd.Parameters.Add(new SqlParameter("@p3", supplier.ContactTitle));
                cmd.Parameters.Add(new SqlParameter("@p4", supplier.Address));
                cmd.Parameters.Add(new SqlParameter("@p5", supplier.City));
                cmd.Parameters.Add(new SqlParameter("@p6", supplier.Region));
                cmd.Parameters.Add(new SqlParameter("@p7", supplier.PostalCode));
                cmd.Parameters.Add(new SqlParameter("@p8", supplier.Country));
                cmd.Parameters.Add(new SqlParameter("@p9", supplier.Phone));
                cmd.Parameters.Add(new SqlParameter("@p10", supplier.Fax));

                if (cmd.ExecuteNonQuery() > -1)
                {
                    return true;
                }
            }
        }

        return false;
    }

    [WebMethod()]
    public bool UpdateProduct(int productID, string productName, int categoryID, int supplierID, bool discontinued)
    {
        const string SQL =  "UPDATE [Products]              " +
                            "SET    [ProductName]   = @p1,  " +
                            "       [SupplierID]    = @p2,  " +
                            "       [CategoryID]    = @p3,  " +
                            "       [Discontinued]  = @p4   " +
                            "WHERE                          " +
                            "       [ProductID] = @p5";

        using (IDbConnection cnn = CreateConnection())
        {
            using (IDbCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = SQL;
                cmd.Parameters.Add(new SqlParameter("@p1", productName));
                cmd.Parameters.Add(new SqlParameter("@p2", ((supplierID > 0) ? (object) supplierID : DBNull.Value)));
                cmd.Parameters.Add(new SqlParameter("@p3", ((categoryID > 0) ? (object) categoryID : DBNull.Value)));
                cmd.Parameters.Add(new SqlParameter("@p4", discontinued));
                cmd.Parameters.Add(new SqlParameter("@p5", productID));

                if (cmd.ExecuteNonQuery() > -1)
                {
                    return true;
                }
            }
        }

        return false;
    }

    [WebMethod()]
    public bool DeleteProduct(int productID)
    {
        const string SQL =  "DELETE [Products]       " +
                            "WHERE                   " +
                            "       [ProductID] = @P1";

        using (IDbConnection cnn = CreateConnection())
        {
            using (IDbCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = SQL;
                cmd.Parameters.Add(new SqlParameter("@p1", productID));

                if (cmd.ExecuteNonQuery() > -1)
                {
                    return true;
                }
            }
        }

        return false;
    }

    [WebMethod()]
    public bool UpdateCustomer(string customerId, string company, string contact, string title)
    {
        const string SQL =  "UPDATE [Customers]               " +
                            "SET    [CompanyName]   = @p1,    " +
                            "       [ContactName]   = @p2,    " +
                            "       [ContactTitle]  = @p3     " +
                            "WHERE                            " +
                            "       [CustomerID] = @p4";

        using (IDbConnection cnn = CreateConnection())
        {
            using (IDbCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = SQL;
                cmd.Parameters.Add(new SqlParameter("@p1", company));
                cmd.Parameters.Add(new SqlParameter("@p2", contact));
                cmd.Parameters.Add(new SqlParameter("@p3", title));
                cmd.Parameters.Add(new SqlParameter("@p4", customerId));

                if (cmd.ExecuteNonQuery() > -1)
                {
                    return true;
                }
            }
        }

        return false;
    }

    [WebMethod()]
    public bool DeleteCustomer(string customerId)
    {
        const string SQL =  "DELETE [Customers]       " +
                            "WHERE                    " +
                            "       [CustomerID] = @P1";

        using (IDbConnection cnn = CreateConnection())
        {
            using (IDbCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = SQL;
                cmd.Parameters.Add(new SqlParameter("@p1", customerId));

                if (cmd.ExecuteNonQuery() > -1)
                {
                    return true;
                }
            }
        }

        return false;
    }

[WebMethod]
    public PagedResult<Product> GetTop100Products(string sortColumn, string sortOrder)
    {
        if (string.IsNullOrEmpty(sortColumn) || (string.Compare(sortColumn, "Name", true) == 0))
        {
            sortColumn = "ProductName";
        }
        else if (string.Compare(sortColumn, "SupplierName") == 0)
        {
            sortColumn = "CompanyName";
        }

        if (string.IsNullOrEmpty(sortOrder))
        {
            sortOrder = "ASC";
        }

        const string SQL = "        SELECT  [P].[ProductID],                                       " +
                           "                [P].[ProductName],                                     " +
                           "                [C].[CategoryID],                                      " +
                           "                [C].[CategoryName] AS [CategoryName],                  " +
                           "                [S].[SupplierID],                                      " +
                           "                [S].[CompanyName] AS [SupplierName],                   " +
                           "                [P].[QuantityPerUnit],                                 " +
                           "                [P].[UnitPrice],                                       " +
                           "                [P].[UnitsInStock],                                    " +
                           "                [P].[UnitsOnOrder],                                    " +
                           "                [P].[Discontinued]                                    " +
                           "         FROM                                                          " +
                           "                [Products] AS [P]                                      " +
                           "         LEFT OUTER JOIN                                               " +
                           "                [Categories] AS [C]                                    " +
                           "         ON     [P].[CategoryID] = [C].[CategoryID]                    " +
                           "         LEFT OUTER JOIN                                               " +
                           "                [Suppliers] AS [S]                                     " +
                           "         ON     [P].[SupplierID] = [S].[SupplierID]                    " +
                           "         ORDER BY {0} {1}                                            ";

        var list = new List<Product>();

        using (IDbConnection cnn = CreateConnection())
        {
            using (IDbCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = string.Format(SQL, sortColumn, sortOrder);

                using (IDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Product p = BuildProduct(rdr);

                        list.Add(p);
                    }
                }
            }
        }

        if ((list.Count == 0))
        {
            return null;
        }

        var result = new PagedResult<Product>();

        result.Rows = list;

        return result;
    }

    private static Supplier BuildSupplier(IDataReader reader)
    {
        Supplier supplier = new Supplier();

        supplier.ID = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
        supplier.Company = reader.IsDBNull(1) ? string.Empty : reader.GetString(1).Trim();
        supplier.ContactName = reader.IsDBNull(2) ? string.Empty : reader.GetString(2).Trim();
        supplier.ContactTitle = reader.IsDBNull(3) ? string.Empty : reader.GetString(3).Trim();
        supplier.Address = reader.IsDBNull(4) ? string.Empty : reader.GetString(4).Trim();
        supplier.City = reader.IsDBNull(5) ? string.Empty : reader.GetString(5).Trim();
        supplier.Region = reader.IsDBNull(6) ? string.Empty : reader.GetString(6).Trim();
        supplier.PostalCode = reader.IsDBNull(7) ? string.Empty : reader.GetString(7).Trim();
        supplier.Country = reader.IsDBNull(8) ? string.Empty : reader.GetString(8).Trim();
        supplier.Phone = reader.IsDBNull(9) ? string.Empty : reader.GetString(9).Trim();
        supplier.Fax = reader.IsDBNull(10) ? string.Empty : reader.GetString(10).Trim();

        return supplier;
    }

    private static Customer BuildCustomer(IDataReader reader)
    {
        Customer customer = new Customer();

        customer.ID = reader.IsDBNull(0) ? string.Empty : reader.GetString(0).Trim();
        customer.Company = reader.IsDBNull(1) ? string.Empty : reader.GetString(1).Trim();
        customer.ContactName = reader.IsDBNull(2) ? string.Empty : reader.GetString(2).Trim();
        customer.ContactTitle = reader.IsDBNull(3) ? string.Empty : reader.GetString(3).Trim();
        customer.Address = reader.IsDBNull(4) ? string.Empty : reader.GetString(4).Trim();
        customer.City = reader.IsDBNull(5) ? string.Empty : reader.GetString(5).Trim();
        customer.PostalCode = reader.IsDBNull(6) ? string.Empty : reader.GetString(6).Trim();
        customer.Country = reader.IsDBNull(7) ? string.Empty : reader.GetString(7).Trim();
        customer.Phone = reader.IsDBNull(8) ? string.Empty : reader.GetString(8).Trim();

        return customer;
    }

    private static Product BuildProduct(IDataReader reader)
    {
        Product product = new Product();

        product.ID = reader.IsDBNull(0) ? int.MinValue : reader.GetInt32(0);
        product.Name = reader.IsDBNull(1) ? string.Empty : reader.GetString(1).Trim();
        product.CategoryID = reader.IsDBNull(2) ? int.MinValue : reader.GetInt32(2);
        product.CategoryName = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
        product.SupplierID = reader.IsDBNull(4) ? int.MinValue : reader.GetInt32(4);
        product.SupplierName = reader.IsDBNull(5) ? string.Empty : reader.GetString(5);
        product.QuantityPerUnit = reader.IsDBNull(6) ? string.Empty : reader.GetString(6).Trim();
        product.UnitPrice = reader.IsDBNull(7) ? decimal.MinValue : reader.GetDecimal(7);
        product.UnitsInStock = reader.IsDBNull(8) ? short.MinValue : reader.GetInt16(8);
        product.UnitsOnOrder = reader.IsDBNull(9) ? short.MinValue : reader.GetInt16(9);
        product.Discontinued = reader.IsDBNull(10) ? false : reader.GetBoolean(10);

        return product;
    }

    private static IDbConnection CreateConnection()
    {
        IDbConnection cnn = new SqlConnection(ConnectionString);
        cnn.Open();

        return cnn;
    }
}
